.Net Core Web Api_筆記13_api結合ADO.NET資料庫操作part1_專案前置準備到資料新增

2021 iThome 鐵人賽

DAY 2

自我挑戰組

.NET Core WebApi網頁應用開發系列第 13 篇

13th鐵人賽

Samuel

2021-09-30 14:46:47

2249 瀏覽

分享至

專案前置準備

新建好資料庫以及資料表

create table NewsType
(
	NewsTypeId int identity primary key,
	NewsTypeName nvarchar(200),
	isEnabled bit
)

create table NewsInfo
(
	NewsId int identity primary key,
	NewsTitle nvarchar(300),
	NewsContent nvarchar(max),
	CreateDate datetime,
	NewsTypeId int foreign key references NewsType(NewsTypeId)
)

建立並配置好visual studio .net core web api專案

配置好資料庫連線設定(位在appsetting.json中)

安裝 Microsoft.Extensions.Configuration.Json
我們會藉由它提供的api做組態檔案讀取跟設置

新增Models、Utility跟Services三目錄
在Services目錄新建一個Class
命名為
AppConfigurationService.cs

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MyNet5ApiAdoTest.Services
{
    public class AppConfigurationService
    {
        public static IConfiguration Configuration { get; set; }
        static AppConfigurationService()
        {
            Configuration = new Microsoft.Extensions.Configuration.ConfigurationBuilder()
                .Add(new JsonConfigurationSource { Path = "appsettings.json", ReloadOnChange = true }).Build();
        }
    }
}

這裡新增一個API控制器-空白名稱取名HomeController.cs
新增 Index Action method

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using MyNet5ApiAdoTest.Services;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MyNet5ApiAdoTest.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class HomeController : ControllerBase
    {
        [HttpGet("index")]
        public string Index()
        {            
            string strConn = AppConfigurationService.Configuration.GetConnectionString("NewsDb");
            return strConn;
        }
    }
}

測試是否能成功取得連線字串

安裝Microsoft.Data.SqlClient

在Utility目錄新增MSSQLHelper (based on ado.net)

using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using MyNet5ApiAdoTest.Services;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace MyNet5ApiAdoTest.Utility
{
    public static class MSSQLHelper
    {
        public static readonly string connectionString = AppConfigurationService.Configuration.GetConnectionString("NewsDb");

        public static int ExecuteNonQuery(string strSQL, Hashtable htParams = null)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand sqlCommand = conn.CreateCommand();
                sqlCommand.CommandText = strSQL;
                sqlCommand.CommandType = CommandType.Text;
                PrepareParams(htParams, sqlCommand);
                return sqlCommand.ExecuteNonQuery();
            }
        }

        public static SqlDataReader GetSqlDataReader(string strSQL, Hashtable htParams = null)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand sqlCommand = conn.CreateCommand();
            sqlCommand.CommandText = strSQL;
            sqlCommand.CommandType = CommandType.Text;
            PrepareParams(htParams, sqlCommand);
            return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public static object ExecuteScalar(string strSQL, Hashtable htParams = null)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand sqlCommand = conn.CreateCommand();
                sqlCommand.CommandText = strSQL;
                sqlCommand.CommandType = CommandType.Text;
                PrepareParams(htParams, sqlCommand);
                return sqlCommand.ExecuteScalar();
            }
        }

        private static void PrepareParams(Hashtable htParams, SqlCommand sqlCommand)
        {
            if(htParams != null)
            {
                foreach (DictionaryEntry entry in htParams)
                {
                    sqlCommand.Parameters.AddWithValue(entry.Key.ToString(), entry.Value);
                }
            }            
        }
    }
}

於Models目錄下準備好兩個DTO Class

NewsType.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MyNet5ApiAdoTest.Models
{
    public class NewsType
    {
        public int NewsTypeId { get; set; }

        public string NewsTypeName { get; set; }

        public bool? isEnabled { get; set; }

    }
}

NewsInfo.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MyNet5ApiAdoTest.Models
{
    public class NewsInfo
    {
        public int NewsId { get; set; }

        public string NewsTitle { get; set; }

        public string NewsContent { get; set; }

        public DateTime? CreateDate { get; set; }

        public int? NewsTypeId { get; set; }
    }
}

NewsType資料新增

新增NewsTypeController

NewsTypeController 先準備一個新增NewsType的Action

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using MyNet5ApiAdoTest.Models;
using MyNet5ApiAdoTest.Utility;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MyNet5ApiAdoTest.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class NewsTypeController : ControllerBase
    {
        [HttpPost("Add")]
        public ActionResult<int> AddNewsType(NewsType newsType)
        {
            int RowCount = 0;
            if (newsType != null)
            {
                string strSQL = @"INSERT INTO NewsType (NewsTypeName,IsEnabled)
                                VALUES (@NewsTypeName,@IsEnabled)";

                Hashtable htParms = new Hashtable();
                htParms.Add("@NewsTypeName",newsType.NewsTypeName);
                htParms.Add("@IsEnabled",newsType.isEnabled);
                RowCount = MSSQLHelper.ExecuteNonQuery(strSQL, htParms);
                return CreatedAtAction(nameof(AddNewsType), RowCount);
            }
            return RowCount;
        }

    }
}

用Postman先測試看看運行起專案
content type設置為application/json
body填入json參數內文

有成功傳進Action

也成功塞入DB table

這裡準備好一個前端html頁面
引入好bs4跟jquery3.6
測試Client端有UI時的互動存取(透過jQuery)

Add.html

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title>Add NewsType</title>
    <link href="../css/bootstrap.min.css" rel="stylesheet" />
    <script src="../js/jquery/jquery.min.js"></script>
</head>
<body>

    <div style="padding:20px;border:1px solid #0094ff;width:600px;margin:30px;">
        <h3>新聞文章類別</h3>
        <hr />
        <div class="form-horizontal">
            <div class="form-group col-4">
                <label>文章類別</label>
                <input type="text" class="form-control" id="NewsTypeName">
            </div>
            <div class="form-group col-4">
                <label>是否啟用</label>
                <input type="checkbox" class="form-check" id="isEnabled">
            </div>
            <div class="form-group">
                <div class="col-md-2 col-md-10">
                    <button type="submit" id="btnSubmit" class="btn btn-primary">Submit</button>
                </div>
                <div>
                    <span id="msg" class="danger"></span>
                </div>
            </div>
        </div>
    </div>
    <script type="text/javascript">
        $('#btnSubmit').click(function(){
            $.ajax({
                type:'post',
                url:"/api/newstype/add",
                dataType:"text",
                data: JSON.stringify({
                    NewsTypeId: 1,
                    NewsTypeName: $('#NewsTypeName').val(),
                    isEnabled: $('#isEnabled').prop('checked')
                }),
                contentType: 'application/json',
                success: function (result) {
                    if (result == "1") {
                        $('#msg').text('成功添加');
                    }
                }
            });
        });
    </script>
</body>
</html>

也可成功存取api添加